package com.sunhao.myapplication.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.sunhao.myapplication.entity.User;

import java.util.ArrayList;
import java.util.List;

public class UserDBHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "user.db";
    private static final String TABLE_NAME = "user_info";
    private static final Integer DB_VERSION = 1;
    private static UserDBHelper mHelper = null;
    private SQLiteDatabase mRDB = null;
    private SQLiteDatabase mWDB = null;

    private UserDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    //利用单例模式获取数据库帮助器的唯一实例
    public static UserDBHelper getInstance(Context context) {
        if (mHelper == null) {
            mHelper = new UserDBHelper(context);
        }
        return mHelper;
    }


    //打开数据库的读连接
    public SQLiteDatabase openReadLink() {
        if (mRDB == null || !mRDB.isOpen()) {
            mRDB = mHelper.getReadableDatabase();
        }
        return mRDB;
    }

    //打开数据库的写连接
    public SQLiteDatabase openWriteLink() {
        if (mWDB == null || !mWDB.isOpen()) {
            mWDB = mHelper.getReadableDatabase();
        }
        return mWDB;
    }


    //关闭数据库连接
    public void closeLink() {
        if (mRDB != null && mRDB.isOpen()) {
            mRDB.close();
            mRDB = null;
        }
        if (mWDB != null && mWDB.isOpen()) {
            mWDB.close();
            mWDB = null;
        }
    }


    //创建数据库,执行建表语句
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME
                + " (_id integer PRIMARY KEY AUTOINCREMENT NOT NULL," +
                "  name varchar not NULL," +
                "  age integer not NULL," +
                "  height long  not NULL," +
                "  weight float not NULL," +
                "  married integer not NULL" +
                ");";
        sqLiteDatabase.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        //当数据库版本发生更新时会触发
        String sql1 = "ALTER TABLE" + TABLE_NAME + "ADD COLUMN phone VARCHAR";
        sqLiteDatabase.execSQL(sql1);
        String sql2 = "ALTER TABLE" + TABLE_NAME + "ADD COLUMN password VARCHAR";
        sqLiteDatabase.execSQL(sql2);
    }


    //添加数据
    public long insert(User user) {
        ContentValues values = new ContentValues();
        values.put("name", user.name);
        values.put("age", user.age);
        values.put("height", user.height);
        values.put("weight", user.weight);
        values.put("married", user.married);

        //开启事务
        try {
            mWDB.beginTransaction();
            mWDB.insert(TABLE_NAME, null, values);
//            如果失败代码,就会进入finally回滚
            //事务成功
            mWDB.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //回滚事务
            mWDB.endTransaction();
        }

        return 1;
    }

    //删除数据
    public long deleteByName(String name) {
        //删除所有
        //mWDB.delete(TABLE_NAME,"1=1",null);

        //根据名字删除
        return mWDB.delete(TABLE_NAME, "name=?", new String[]{name});
    }

    //修改数据
    public long update(User user) {
        ContentValues values = new ContentValues();
        values.put("name", user.name);
        values.put("age", user.age);
        values.put("height", user.height);
        values.put("weight", user.weight);
        values.put("married", user.married);
        return mWDB.update(TABLE_NAME, values, "name=?", new String[]{user.name});
    }

    //查询
    public List<User> query() {
        ArrayList<User> list = new ArrayList<>();
        //执行记录查询动作,使语句返回结果集的游标
        Cursor cursor = mRDB.query(TABLE_NAME, null, null, null, null, null, null);

        //  通过名字查询
        //  Cursor cursor = mRDB.query(TABLE_NAME, null, "name?=", new String[]{name}, null, null, null);
        //循环取出游标指向每条记录
        while (cursor.moveToNext()) {
            User user1 = new User();
            user1.id = cursor.getInt(0);
            user1.name = cursor.getString(1);
            user1.age = cursor.getInt(2);
            user1.height = cursor.getLong(3);
            user1.weight = cursor.getFloat(4);
            user1.married = cursor.getInt(5) == 0 ? false : true;
            list.add(user1);
        }
        return list;
    }

}
